﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListAnnouncement')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListAnnouncement'
    DROP  Procedure  proc_cm_ListAnnouncement
END
GO

PRINT 'Creating Procedure proc_cm_ListAnnouncement'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListAnnouncement]
	@pHtmlPageId uniqueidentifier
	, @pSectionCd nvarchar(50) = 'Main'
	, @pShowPending bit = 0
AS
BEGIN
	SELECT
		a.[announcement_id]
		,a.[announcement_title_txt]
		,a.[announcement_txt]
		,a.[effective_start_dttm]
		,a.[effective_end_dttm]
		,a.[create_dttm]
		,a.[update_dttm]
		,ISNULL(a.[effective_start_dttm], a.[create_dttm]) as [display_dttm]
		,ISNULL(ISNULL(m.[korean_nm], u.[UserName]), N'관리자') as [user_display_nm]
	FROM
		[dbo].[tbl_announcement] a (NOLOCK)
		LEFT JOIN [dbo].[aspnet_Users] u (NOLOCK) ON a.UserId = u.UserId
		LEFT JOIN [dbo].[tbl_member] m (NOLOCK) ON u.UserId = m.UserId
	WHERE
		a.[html_page_id] = @pHtmlPageId
		and a.[section_cd] = @pSectionCd
		and ((@pShowPending = 1 and GETDATE() < isnull(a.[effective_end_dttm], DATEADD(day, 1, GETDATE())))
			or (a.[effective_start_dttm] is null and a.[effective_end_dttm] is null)
			or (a.[effective_start_dttm] is null and GETDATE() < a.[effective_end_dttm])
			or (a.[effective_end_dttm] is null and GETDATE() > a.[effective_start_dttm])
			or (getdate() between a.[effective_start_dttm] and a.[effective_end_dttm]))
	ORDER BY
		ISNULL(a.[effective_start_dttm], a.[create_dttm]) DESC
END
GO

GRANT EXEC ON dbo.proc_cm_ListAnnouncement TO PUBLIC
GO
